<?php
/**
 * 用MySQLi将CSV导入MySQL数据库的示例
 * 注意，在这个示例中，如果表中有值为空的字段是插入不进去的，需要再看一下prepare的方法
 * 另外在插入记录时还需要填写绑定字符串，此字符串一个字符后面相应字段的变量类型，这个例子中有八个字符，因此，用8个s表示
 * User: ford
 * Date: 15-6-25
 * Time: 上午11:11
 */
if($argc != 3) {
	die("USAGE:mysqlcreate.php <table_name> <file_name>\n");

}

$table_name = $argv[1];
$file_name = $argv[2];
$row_num = 0;

function create_insert_stmt($table, $ncols) {
	$stmt = "INSERT INTO $table VALUES(";
	foreach(range(1,$ncols) as $i) {
		$stmt .= "?,";
	}
	$stmt = preg_replace("/,$/", ')', $stmt);
//	echo $stmt;
	return$stmt;
}

try {
	$db = new mysqli('localhost', 'root', 'al8840dd', 'scott');
	$db->autocommit(false);		//关闭自动提交模式，用于事务处理
	$res = $db->prepare("SELECT * FROM $table_name");

	if($db->errno != 0) {
		throw new Exception($db->error);
	}
	$ncols = $res->field_count;
	$res->free_result();
	$ins = create_insert_stmt($table_name, $ncols);
	$fmt = str_repeat('s', $ncols);
	$res = $db->prepare($ins);
	if($db->errno != 0) {
		throw new Exception($db->error);
	}

	$fp =new SplFileObject($file_name, 'r');
	while($row = $fp->fgetcsv()) {
		if(strlen(implode('', $row)) == 0) {
			continue;
		}

		array_unshift($row, $fmt);
		foreach(range(1, $ncols) as $i) {
			$row[$i] = &$row[$i];
		}

		call_user_func_array(array($res, "bind_param"), $row);
		$res->execute();

		if ($res->errno != 0) {
			print_r($row);
			throw new Exception($res->error);
		}

		$row_num++;
	}

	$db->commit();
	if($db->errno != 0) {
		throw new Exception($db->error);
	}

	echo "$row_num rows inserted into $table_name.\n";
}
catch (Exception $e) {
	echo "Exception:\n";
	die($e->getMessage() . "\n");
}